using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using PipelineTemplateEditor.Util;

namespace PipelineTemplateEditor
{
    public partial class TemplateEditor : Form
    {
        List<ComboBoxItem> fieldTypes = new List<ComboBoxItem>();
        private bool isNew = true;
        private int templateID = -1;

        #region SQL strings
        private string templateSelect = "SELECT [row_template_id] " +
                "      ,[row_template_name] " +
                "      ,[row_template_size] " +
                "      ,[row_template_item_summed] " +
                "      ,[h1] " +
                "      ,[f11] " +
                "      ,[f12] " +
                "      ,[h2] " +
                "      ,[f21] " +
                "      ,[f22] " +
                "      ,[h3] " +
                "      ,[f31] " +
                "      ,[f32] " +
                "      ,[h4] " +
                "      ,[f41] " +
                "      ,[f42] " +
                "      ,[h5] " +
                "      ,[f51] " +
                "      ,[f52] " +
                "      ,[h6] " +
                "      ,[f61] " +
                "      ,[f62] " +
                "      ,[h7] " +
                "      ,[f71] " +
                "      ,[f72] " +
                "      ,[h8] " +
                "      ,[f81] " +
                "      ,[f82] " +
                "      ,[h9] " +
                "      ,[f91] " +
                "      ,[f92] " +
                "      ,[h10] " +
                "      ,[f101] " +
                "      ,[f102] " +
                "      ,[h11] " +
                "      ,[f111] " +
                "      ,[f112] " +
                "      ,[h12] " +
                "      ,[f121] " +
                "      ,[f122] " +
                "      ,[h13] " +
                "      ,[f131] " +
                "      ,[f132] " +
                "      ,[h14] " +
                "      ,[f141] " +
                "      ,[f142] " +
                "      ,[h15] " +
                "      ,[f151] " +
                "      ,[f152] " +
                "      ,[h16] " +
                "      ,[f161] " +
                "      ,[f162] " +
                "      ,[h17] " +
                "      ,[f171] " +
                "      ,[f172] " +
                "      ,[h18] " +
                "      ,[f181] " +
                "      ,[f182] " +
                "      ,[h19] " +
                "      ,[f191] " +
                "      ,[f192] " +
                "      ,[h20] " +
                "      ,[f201] " +
                "      ,[f202] " +
                "      ,[row_template_where] " +
                "      ,[row_template_group_by] " +
                "      ,[row_template_sort_by] " +
                "  FROM [row_template] ";

        private string templateUpdate = "UPDATE [row_template] " +
                "   SET [row_template_name] = {0} " +
                "      ,[row_template_size] = {1} " +
                "      ,[row_template_item_summed] = {2} " +
                "      ,[h1] = {3} " +
                "      ,[f11] = {4} " +
                "      ,[f12] = {5} " +
                "      ,[h2] = {6} " +
                "      ,[f21] = {7} " +
                "      ,[f22] = {8} " +
                "      ,[h3] = {9} " +
                "      ,[f31] = {10} " +
                "      ,[f32] = {11} " +
                "      ,[h4] = {12} " +
                "      ,[f41] = {13} " +
                "      ,[f42] = {14} " +
                "      ,[h5] = {15} " +
                "      ,[f51] = {16} " +
                "      ,[f52] = {17} " +
                "      ,[h6] = {18} " +
                "      ,[f61] = {19} " +
                "      ,[f62] = {20} " +
                "      ,[h7] = {21} " +
                "      ,[f71] = {22} " +
                "      ,[f72] = {23} " +
                "      ,[h8] = {24} " +
                "      ,[f81] = {25} " +
                "      ,[f82] = {26} " +
                "      ,[h9] = {27} " +
                "      ,[f91] = {28} " +
                "      ,[f92] = {29} " +
                "      ,[h10] = {30} " +
                "      ,[f101] = {31} " +
                "      ,[f102] = {32} " +
                "      ,[h11] = {33} " +
                "      ,[f111] = {34} " +
                "      ,[f112] = {35} " +
                "      ,[h12] = {36} " +
                "      ,[f121] = {37} " +
                "      ,[f122] = {38} " +
                "      ,[h13] = {39} " +
                "      ,[f131] = {40} " +
                "      ,[f132] = {41} " +
                "      ,[h14] = {42} " +
                "      ,[f141] = {43} " +
                "      ,[f142] = {44} " +
                "      ,[h15] = {45} " +
                "      ,[f151] = {46} " +
                "      ,[f152] = {47} " +
                "      ,[h16] = {48} " +
                "      ,[f161] = {49} " +
                "      ,[f162] = {50} " +
                "      ,[h17] = {51} " +
                "      ,[f171] = {52} " +
                "      ,[f172] = {53} " +
                "      ,[h18] = {54} " +
                "      ,[f181] = {55} " +
                "      ,[f182] = {56} " +
                "      ,[h19] = {57} " +
                "      ,[f191] = {58} " +
                "      ,[f192] = {59} " +
                "      ,[h20] = {60} " +
                "      ,[f201] = {61} " +
                "      ,[f202] = {62} " +
                "      ,[row_template_where] = {63} " +
                "      ,[row_template_group_by] = {64} " +
                "      ,[row_template_sort_by] = {65} ";

        private string templateInsert = " INSERT INTO [row_template]" + 
               " ([row_template_name]" + 
               " ,[row_template_size]" + 
               " ,[row_template_item_summed]" + 
               " ,[h1]" + 
               " ,[f11]" + 
               " ,[f12]" + 
               " ,[h2]" + 
               " ,[f21]" + 
               " ,[f22]" + 
               " ,[h3]" + 
               " ,[f31]" + 
               " ,[f32]" + 
               " ,[h4]" + 
               " ,[f41]" + 
               " ,[f42]" + 
               " ,[h5]" + 
               " ,[f51]" + 
               " ,[f52]" + 
               " ,[h6]" + 
               " ,[f61]" + 
               " ,[f62]" + 
               " ,[h7]" + 
               " ,[f71]" + 
               " ,[f72]" + 
               " ,[h8]" + 
               " ,[f81]" + 
               " ,[f82]" + 
               " ,[h9]" + 
               " ,[f91]" + 
               " ,[f92]" + 
               " ,[h10]" + 
               " ,[f101]" + 
               " ,[f102]" + 
               " ,[h11]" + 
               " ,[f111]" + 
               " ,[f112]" + 
               " ,[h12]" + 
               " ,[f121]" + 
               " ,[f122]" + 
               " ,[h13]" + 
               " ,[f131]" + 
               " ,[f132]" + 
               " ,[h14]" + 
               " ,[f141]" + 
               " ,[f142]" + 
               " ,[h15]" + 
               " ,[f151]" + 
               " ,[f152]" + 
               " ,[h16]" + 
               " ,[f161]" + 
               " ,[f162]" + 
               " ,[h17]" + 
               " ,[f171]" + 
               " ,[f172]" + 
               " ,[h18]" + 
               " ,[f181]" + 
               " ,[f182]" + 
               " ,[h19]" + 
               " ,[f191]" + 
               " ,[f192]" + 
               " ,[h20]" + 
               " ,[f201]" + 
               " ,[f202]" + 
               " ,[row_template_where]" + 
               " ,[row_template_group_by]" + 
               " ,[row_template_sort_by])" + 
		       "    VALUES" +
               " ({0}" +
               " ,{1}" +
               " ,{2}" +
                ",{3} " +
                ",{4} " +
                ",{5} " +
                ",{6} " +
                ",{7} " +
                ",{8} " +
                ",{9} " +
                ",{10} " +
                ",{11} " +
                ",{12} " +
                ",{13} " +
                ",{14} " +
                ",{15} " +
                ",{16} " +
                ",{17} " +
                ",{18} " +
                ",{19} " +
                ",{20} " +
                ",{21} " +
                ",{22} " +
                ",{23} " +
                ",{24} " +
                ",{25} " +
                ",{26} " +
                ",{27} " +
                ",{28} " +
                ",{29} " +
                ",{30} " +
                ",{31} " +
                ",{32} " +
                ",{33} " +
                ",{34} " +
                ",{35} " +
                ",{36} " +
                ",{37} " +
                ",{38} " +
                ",{39} " +
                ",{40} " +
                ",{41} " +
                ",{42} " +
                ",{43} " +
                ",{44} " +
                ",{45} " +
                ",{46} " +
                ",{47} " +
                ",{48} " +
                ",{49} " +
                ",{50} " +
                ",{51} " +
                ",{52} " +
                ",{53} " +
                ",{54} " +
                ",{55} " +
                ",{56} " +
                ",{57} " +
                ",{58} " +
                ",{59} " +
                ",{60} " +
                ",{61} " +
                ",{62} " +
                " ,{63}" + 
                " ,{64}" +
                " ,{65}); SELECT SCOPE_IDENTITY();";
        #endregion

        public TemplateEditor()
        {
            InitializeComponent();
            _populateControls();
            saveToolStripMenuItem.Text = "Insert";
            populateForm(-1);
        }

        private void _populateControls()
        {
            // TODO: Replace with data reader
            //fieldTypes.Add(new ComboBoxItem("deal_name", "Deal Name"));
            //fieldTypes.Add(new ComboBoxItem("deal_city", "City"));
            //fieldTypes.Add(new ComboBoxItem("deal_state", "State"));
            fieldTypes = ConnectionManager.GetFieldsList();
            //...
            lbAvailableFields.DataSource = fieldTypes;

            // Add rows to grid
            dgMain.Rows.Add(3);
        }

        private void OnAddClick(object sender, EventArgs e)
        {
            _addFieldToGrid();
        }

        private void OnListMouseDblClick(object sender, MouseEventArgs e)
        {
            _addFieldToGrid();
        }

        private void _addFieldToGrid()
        {
            if (lbAvailableFields.SelectedIndex >= 0 &&
                dgMain.SelectedCells.Count > 0)
            {
                foreach (DataGridViewCell cell in dgMain.SelectedCells)
                {
                    cell.Value = ((ComboBoxItem)lbAvailableFields.SelectedItem).Label;
                    cell.Tag = ((ComboBoxItem)lbAvailableFields.SelectedItem).Value;
                }
            }
        }

        private void OnCellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && e.RowIndex >= 0)
            {
                DataGridViewCell cell = dgMain.Rows[e.RowIndex].Cells[e.ColumnIndex];
                if (cell.Value == null || String.IsNullOrEmpty(cell.Value.ToString()))
                {
                    cell.Style.BackColor = Color.LightGray;
                    cell.Tag = null;
                }
                else if (GetValueFromLabel(cell.Value.ToString()) != null
                    && e.RowIndex != 0)
                {
                    cell.Style.BackColor = Color.LightGreen;
                    cell.Tag = GetValueFromLabel(cell.Value.ToString());
                }
                else
                {
                    cell.Style.BackColor = Color.LightSalmon;
                    cell.Tag = null;
                }
            }
        }

        private string GetValueFromLabel(string name)
        {
            foreach (ComboBoxItem item in fieldTypes)
            {
                if (item.Label == name)
                    return item.Value;
            }
            return null;
        }

        private string GetLabelFromValue(string value)
        {
            foreach (ComboBoxItem item in fieldTypes)
            {
                if (item.Value == value)
                    return item.Label;
            }
            return null;
        }

        private void insertColumnToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dgMain.SelectedCells.Count > 0 &&
                (dgMain.Rows[0].Cells[dgMain.ColumnCount - 1].Value == null || dgMain.Rows[0].Cells[dgMain.ColumnCount - 1].Value.ToString() == "") &&
                (dgMain.Rows[1].Cells[dgMain.ColumnCount - 1].Value == null || dgMain.Rows[1].Cells[dgMain.ColumnCount - 1].Value.ToString() == "") &&
                (dgMain.Rows[2].Cells[dgMain.ColumnCount - 1].Value == null || dgMain.Rows[2].Cells[dgMain.ColumnCount - 1].Value.ToString() == ""))
            {
                int leftIndex = dgMain.SelectedCells[0].ColumnIndex;
                for (int i = dgMain.ColumnCount - 2; i >= leftIndex; i--)
                {
                    dgMain.Rows[0].Cells[i + 1].Value = dgMain.Rows[0].Cells[i].Value;
                    dgMain.Rows[0].Cells[i + 1].Tag = dgMain.Rows[0].Cells[i].Tag;
                    dgMain.Rows[1].Cells[i + 1].Value = dgMain.Rows[1].Cells[i].Value;
                    dgMain.Rows[1].Cells[i + 1].Tag = dgMain.Rows[1].Cells[i].Tag;
                    dgMain.Rows[2].Cells[i + 1].Value = dgMain.Rows[2].Cells[i].Value;
                    dgMain.Rows[2].Cells[i + 1].Tag = dgMain.Rows[2].Cells[i].Tag;
                }
                dgMain.Rows[0].Cells[leftIndex].Value = null;
                dgMain.Rows[0].Cells[leftIndex].Tag = null;
                dgMain.Rows[1].Cells[leftIndex].Value = null;
                dgMain.Rows[1].Cells[leftIndex].Tag = null;
                dgMain.Rows[2].Cells[leftIndex].Value = null;
                dgMain.Rows[2].Cells[leftIndex].Tag = null;
            }
        }

        private void PasteFromClipboard()
        {
            if (Clipboard.GetText() != null && dgMain.SelectedCells.Count > 0)
            {
                DataGridViewCell startCell = dgMain.SelectedCells[0];
                string[] lines = Clipboard.GetText().Split('\n');
                for (int j = 0; j < lines.Length; j++)
                {
                    try
                    {
                        string[] words = lines[j].Split('\t');
                        for (int i = 0; i < words.Length; i++)
                        {
                            if ((dgMain.RowCount >= (startCell.RowIndex + j - 1)) &&
                                (dgMain.ColumnCount >= (startCell.ColumnIndex + i - 1)))
                                dgMain.Rows[startCell.RowIndex + j].Cells[startCell.ColumnIndex + i].Value = words[i];
                        }
                    }
                    catch (Exception ex)
                    {
                        tsStatus.Text = "Error: " + ex.Message;
                    }
                }
            }
        }

        private void OnKeyPreview(object sender, PreviewKeyDownEventArgs e)
        {
            if (e.KeyCode == Keys.Delete)
            {
                foreach (DataGridViewCell cell in dgMain.SelectedCells)
                {
                    cell.Value = null;
                }
            }

            if (e.KeyCode == Keys.V &&
                e.Control)
            {
                PasteFromClipboard();
            }
        }

        private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            OpenDialog form = new OpenDialog();
            DialogResult result = form.ShowDialog(this);
            tsStatus.Text = "";
            if (result == DialogResult.OK)
            {
                if (form.TemplateID > -1)
                {
                    templateID = form.TemplateID;
                    saveToolStripMenuItem.Text = "Save";
                    isNew = false;
                    populateForm(templateID);
                }
            }
        }

        private void populateForm(int id)
        {
            if (id == -1)
            {
                isNew = true;
                tbGroupBy.Text = "";
                tbItemSummed.Text = "";
                tbName.Text = "<new name>";
                tbSortBy.Text = "";
                tbWhere.Text = "";
                tbSize.Text = "";
                lbAvailableFields.SelectedIndex = 0;
                for (int i = 0; i < dgMain.Rows.Count; i++)
                {
                    foreach (DataGridViewCell cell in dgMain.Rows[i].Cells)
                    {
                        cell.Value = null;
                        cell.Tag = null;
                    }
                }
            }
            else
            {
                SqlDataReader dr = null;
                int counter = 0;
                try
                {
                    SqlConnection cn = new SqlConnection(Util.ConnectionManager.ConnectionString);
                    cn.Open();
                    string sql = templateSelect + " WHERE row_template_id = " + id.ToString();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    dr = cmd.ExecuteReader();
                    dr.Read();
                    tbName.Text = (dr.IsDBNull(1) ? null : dr.GetString(1));
                    tbSize.Text = (dr.IsDBNull(2) ? null : dr.GetInt32(2).ToString());
                    tbItemSummed.Text = (dr.IsDBNull(3) ? null : dr.GetString(3));

                    counter = 4;
                    for (int j = 0; j < 20; j++)
                    {
                        for (int i = 0; i < 3; i++)
                        {
                            dgMain.Rows[i].Cells[j].Tag = (dr.IsDBNull(counter) ? null : dr.GetString(counter));
                            string value;
                            if (dr.IsDBNull(counter))
                                value = null;
                            else
                                value = GetLabelFromValue(dr.GetString(counter));
                            dgMain.Rows[i].Cells[j].Value = (value == null ? (dr.IsDBNull(counter) ? null : dr.GetString(counter)) : value);
                            counter++;
                        }
                    }

                    tbWhere.Text = (dr.IsDBNull(64) ? null : dr.GetString(64));
                    tbGroupBy.Text = (dr.IsDBNull(65) ? null : dr.GetString(65));
                    tbSortBy.Text = (dr.IsDBNull(66) ? null : dr.GetString(66));
                    dr.Close();
                }
                catch (Exception ex)
                {
                    tsStatus.Text = "Error: " + ex.Message;
                }
                finally
                {
                    if (dr != null && !dr.IsClosed)
                        dr.Close();
                }
            }
        }

        private bool validateData()
        {
            if (String.IsNullOrEmpty(tbSize.Text))
            {
                MessageBox.Show("Size may not be empty.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            int size = 0;
            if (!int.TryParse(tbSize.Text, out size))
            {
                MessageBox.Show("Size should be a number.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            if (String.IsNullOrEmpty(tbName.Text))
            {
                MessageBox.Show("Name may not be empty.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        private void saveToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (!validateData())
            {
                return;
            }
            SqlConnection cn = new SqlConnection(Util.ConnectionManager.ConnectionString);
            if (isNew)
            {
                try
                {
                    cn.Open();
                    string sql = templateInsert;

                    string[] parameters = new string[66];

                    parameters[0] = "'" + tbName.Text.Replace("'", "''") + "'";
                    parameters[1] = tbSize.Text;
                    parameters[2] = "'" + tbItemSummed.Text.Replace("'", "''") + "'";

                    int counter = 3;
                    for (int j = 0; j < 20; j++)
                    {
                        for (int i = 0; i < 3; i++)
                        {
                            if (dgMain.Rows[i].Cells[j].Tag == null
                                && dgMain.Rows[i].Cells[j].Value != null
                                && dgMain.Rows[i].Cells[j].Value != "")
                                parameters[counter] = "'" + dgMain.Rows[i].Cells[j].Value.ToString().Replace("'", "''") + "'";
                            else if (dgMain.Rows[i].Cells[j].Tag == null
                                && (dgMain.Rows[i].Cells[j].Value == null
                                || dgMain.Rows[i].Cells[j].Value == ""))
                                parameters[counter] = "'-'";
                            else
                                parameters[counter] = "'" + dgMain.Rows[i].Cells[j].Tag.ToString().Replace("'", "''") + "'";
                            counter++;
                        }
                    }

                    parameters[63] = "'" + tbWhere.Text.Replace("'", "''") + "'";
                    parameters[64] = "'" + tbGroupBy.Text.Replace("'", "''") + "'";
                    parameters[65] = "'" + tbSortBy.Text.Replace("'", "''") + "'";
                    sql = String.Format(sql, parameters);
                    sql = sql.Replace("'", "''");
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    object oId = cmd.ExecuteScalar();
                    if (oId == null)
                        tsStatus.Text = "Insert failed.";
                    else
                    {
                        tsStatus.Text = "Successfuly inserted a new record. (" + DateTime.Now + ")";
                        templateID = int.Parse(oId.ToString());
                        isNew = false;
                    }

                }
                catch (Exception ex)
                {
                    tsStatus.Text = "Error: " + ex.Message;
                }
                finally
                {
                    if (cn != null && cn.State != ConnectionState.Closed)
                        cn.Close();
                }
            }
            else if (templateID != -1)
            {
                try
                {
                    cn.Open();
                    string sql = templateUpdate + " WHERE row_template_id = " + templateID;

                    string[] parameters = new string[66];

                    parameters[0] = "'" + tbName.Text.Replace("'", "''") + "'";
                    parameters[1] = tbSize.Text;
                    parameters[2] = "'" + tbItemSummed.Text.Replace("'", "''") + "'";

                    int counter = 3;
                    for (int j = 0; j < 20; j++)
                    {
                        for (int i = 0; i < 3; i++)
                        {
                            if (dgMain.Rows[i].Cells[j].Tag == null
                                && dgMain.Rows[i].Cells[j].Value != null
                                && dgMain.Rows[i].Cells[j].Value != "")
                                parameters[counter] = "'" + dgMain.Rows[i].Cells[j].Value.ToString().Replace("'", "''") + "'";
                            else if (dgMain.Rows[i].Cells[j].Tag == null
                                && (dgMain.Rows[i].Cells[j].Value == null
                                || dgMain.Rows[i].Cells[j].Value == ""))
                                parameters[counter] = "'-'";
                            else
                                parameters[counter] = "'" + dgMain.Rows[i].Cells[j].Tag.ToString().Replace("'", "''") + "'";
                            counter++;
                        }
                    }

                    parameters[63] = "'" + tbWhere.Text.Replace("'", "''") + "'";
                    parameters[64] = "'" + tbGroupBy.Text.Replace("'", "''") + "'";
                    parameters[65] = "'" + tbSortBy.Text.Replace("'", "''") + "'";
                    sql = String.Format(sql, parameters);

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    int qty = cmd.ExecuteNonQuery();
                    tsStatus.Text = "Updated " + qty + " records. (" + DateTime.Now + ")";
                }
                catch (Exception ex)
                {
                    tsStatus.Text = "Error: " + ex.Message;
                }
                finally
                {
                    if (cn != null && cn.State != ConnectionState.Closed)
                        cn.Close();
                }
            }
        }

        private void newToolStripMenuItem_Click(object sender, EventArgs e)
        {
            templateID = -1;
            saveToolStripMenuItem.Text = "Insert";
            isNew = true;
            populateForm(-1);
        }

        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (!isNew &&
                templateID != -1)
            {
                if (MessageBox.Show("Are you sure you want to delete this template?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    SqlConnection cn = new SqlConnection(Util.ConnectionManager.ConnectionString);
                    try
                    {
                        cn.Open();
                        string sql = "DELETE FROM row_template WHERE row_template_id = " + templateID;

                        SqlCommand cmd = new SqlCommand(sql, cn);
                        int qty = cmd.ExecuteNonQuery();
                        tsStatus.Text = "Deleted " + qty + " records. (" + DateTime.Now + ")";
                        isNew = true;
                        templateID = -1;
                        saveToolStripMenuItem.Text = "Insert";
                        populateForm(-1);
                    }
                    catch (Exception ex)
                    {
                        tsStatus.Text = "Error: " + ex.Message;
                    }
                    finally
                    {
                        if (cn != null && cn.State != ConnectionState.Closed)
                            cn.Close();
                    }
                }
            }
        }

        private void cloneToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            tbName.Text = "<enter name>";
            isNew = true;
            templateID = -1;
            saveToolStripMenuItem.Text = "Insert";
            tsStatus.Text = "";
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}